/**
 * Copyright (c) 2005-2016, yinwer81 (yinwer81@aliyun.com)
 *
 * Licensed under the Apache License, Version 2.0
 */
package com.nozturn.throne.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import com.nozturn.throne.dao.UserDao;
import com.nozturn.throne.entity.User;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public User createUser(final User user) {
        final String sql = "insert into tbl_user(username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by) values(?,?,?,?,right(UUID_SHORT(),16),?,?,now(),now(),?,?)";

        final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[] { "pk" });
                int count = 1;
                psst.setString(count++, user.getUsername());
                psst.setString(count++, user.getPassword());
                psst.setString(count++, user.getSalt());
                psst.setString(count++, user.getMobile());
                psst.setInt(count++, user.getUsertype());
                psst.setInt(count++, user.getValid());
                psst.setString(count++, user.getCreatedBy());
                psst.setString(count++, user.getUpdatedBy());
                return psst;
            }
        }, keyHolder);

        user.setPk(keyHolder.getKey().longValue());
        return user;
    }

    public Long bindUserToClient(final Long clientPk, final Long userPk) {
        final String sql = "insert into tbl_client_user(client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by) values(?,?, 200, now(),now(),?,?)";

        final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[] { "pk" });
                int count = 1;
                psst.setLong(count++, clientPk);
                psst.setLong(count++, userPk);
                psst.setString(count++, "system");
                psst.setString(count++, "system");
                return psst;
            }
        }, keyHolder);

        return keyHolder.getKey().longValue();
    }

    public User findUsersByUsernameAndClientPk(final String username, final Long clientPk) {
        final String sql = "select pk, username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by from tbl_user where username=? and valid=200 and pk in (select cu.user_pk from tbl_client_user cu where cu.valid=200 and cu.client_pk=?)";
        final List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), username, clientPk);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }
    
    public int disableClientUser(final Long userPk) {
        final String sql = "update tbl_client_user set valid=100 where user_pk = ?";
        int updated = jdbcTemplate.update(sql, userPk);
        return updated;
    }

    public int disableClientUser(final Long clientPk, final Long userPk) {
        final String sql = "update tbl_client_user set valid=100 where client_pk = ? and user_pk = ?";
        int updated = jdbcTemplate.update(sql, clientPk, userPk);
        return updated;
    }

    public int updateUser(final User user) {
        final String sql = "update tbl_user set username=?, password=?, salt=?, mobile=?, usertype=?, valid=?, updated_at=now(), updated_by=? where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getSalt(), user.getMobile(), user.getUsertype(),
                user.getValid(), user.getUpdatedBy(), user.getPk());
        return updated;
    }

    public User findOne(final Long pk) {
        final String sql = "select pk, username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by from tbl_user where valid=200 and pk = ?";
        final List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), pk);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }

    public List<User> findAll() {
        final String sql = "select pk, username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by from tbl_user where valid=200";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
    }

    public List<User> findByUser(final User user) {
        final String sql = "select pk, username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by from tbl_user where valid=200 and (? is null or pk = ?) and (? is null or username like ?)";
        final List<User> userList = new ArrayList<User>();
        jdbcTemplate.query(sql, new Object[] { user.getPk(), user.getPk(), user.getUsername(), user.getUsername() }, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                User user = new User();
                user.setPk(rs.getLong("pk"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setSalt(rs.getString("salt"));
                user.setMobile(rs.getString("mobile"));
                user.setClientUuid(rs.getLong("client_uuid"));
                user.setCreatedAt(rs.getDate("created_at"));
                user.setCreatedBy(rs.getString("created_by"));
                user.setUpdatedAt(rs.getDate("updated_at"));
                user.setUpdatedBy(rs.getString("updated_by"));
                userList.add(user);
            }
        });
        return userList;
    }

    public int deleteUser(final Long pk) {
        final String sql = "update tbl_user set valid=100, updated_at=now(), updated_by='system' where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, pk);
        return updated;
    }

    public User findByUsername(final String username) {
        final String sql = "select pk, username, password, salt, mobile, client_uuid, usertype, valid, created_at, updated_at, created_by, updated_by from tbl_user where valid=200 and username = ?";
        final List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), username);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }

}
